package com.deng.etl.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;

import com.deng.etl.common.IEtlInterface;
import com.deng.etl.common.EtlLog;
//删除
public class ImportFeeDetails_zhuyuan  extends ImportBase implements IEtlInterface {
	//6685983
	public static int TOPN1=5000;
	
	public static String COLUMNS="id,fee_year,fee_month,开方科室号,开方科室,开方医生号,开方医生,执行科室号,执行科室" +
			",执行人号,执行人,医疗组号,医疗组,费别代码,费别,item_code,item_name,qty,TOT_COST";
	
	public static String SELECT="select "+COLUMNS+"  from neusoft.JIXIAO_FEE_DETAILS_ZHUYUAN";
	
	public static String INSERT="insert into t_org_organization_item_count_month" +
			"(stat_time,create_time,org_order_organization_id,org_order_organization_name,org_order_doctor_id," +
			"org_order_doctor_name,org_operate_organization_id,org_operate_organization_name,org_operate_doctor_id," +
			"org_operate_doctor_name,org_work_group_id,org_work_group_name,org_fee_type_id,org_fee_type_name," +
			"org_item_id,org_item_name,org_item_count,sjly,fee) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
	
	public static String DELETE="delete from t_org_organization_item_count_month where stat_time=? ";	
	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		
	}

	public int getResultSet(ResultSet rs,String arr,int size){
		if(size<TOPN1){
			return size;
		}
		int ret=0;
		String pri="";
		try {
			for(int i=size;i>0;i--){
				rs.absolute(i);
				if(!pri.equals(rs.getString(arr))&&i!=size){
					ret=i;
					break;
				}
				
				pri=rs.getString(arr);
			}
			rs.beforeFirst();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return ret;
	}
	
	
	public void insertData(Connection sourceConn,Connection targetConn,String name,String fromDate,String toDate) {
		System.out.println("从："+fromDate+"   到："+toDate);
		
		Date from=null;
		Date to=null;
		try {
			from = format_s.parse(fromDate);
			to=format_s.parse(toDate);
		} catch (ParseException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		String sql=SELECT+" where fee_year="+(from.getYear()+1900)+" and fee_month="+(from.getMonth()+1)+" and id>? " +
		"order by id FETCH FIRST "+TOPN1+" ROWS ONLY";
		System.out.println(sql);
		try {
			PreparedStatement pst=sourceConn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
			
			PreparedStatement pst_insert=targetConn.prepareStatement(INSERT);
			
			String index="0";
			int sum=0;
		
			while(true){
				pst.setString(1, index);
				ResultSet rs=pst.executeQuery();
				rs.last();
				int size=rs.getRow();
				rs.beforeFirst();

				int i=getResultSet(rs,"id",size);
				int n=0;
				while(rs.next()&&n<i){
					sum++;
					n++;
					String id=rs.getString("id");
					System.out.println("sum=="+sum+";id="+id);
					String item_code=rs.getString("item_code");
					String dep_code=rs.getString("开方科室号");
					String dep_code1=rs.getString("执行科室号");
					java.sql.Date date=new java.sql.Date(from.getYear(),from.getMonth(),1);
					
					if(n==i){
						index=id;
					}
				
					pst_insert.setDate(1, date);
					Date datetmp=new Date();
					pst_insert.setTimestamp(2, getTimestamp(datetmp));
					pst_insert.setString(3, dep_code);
					pst_insert.setString(4, rs.getString("开方科室"));
					pst_insert.setString(5, rs.getString("开方医生号"));
					pst_insert.setString(6, rs.getString("开方医生"));
					pst_insert.setString(7, rs.getString("执行科室号"));
					pst_insert.setString(8, rs.getString("执行科室"));
					pst_insert.setString(9, rs.getString("执行人号"));
					pst_insert.setString(10, rs.getString("执行人"));
					pst_insert.setString(11, rs.getString("医疗组号"));
					pst_insert.setString(12, rs.getString("医疗组"));
					pst_insert.setString(13, rs.getString("费别代码"));
					pst_insert.setString(14, rs.getString("费别"));
					pst_insert.setString(15, rs.getString("item_code"));
					pst_insert.setString(16, rs.getString("item_name"));
					pst_insert.setInt(17, rs.getInt("qty"));
					pst_insert.setInt(18, 1);
					pst_insert.setDouble(19, rs.getDouble("TOT_COST"));
//					insert(pst_insert);
					
					pst_insert.addBatch();

				}
				pst_insert.executeBatch();
				rs.close();
				if(size<TOPN1)
					break;
			}
			
			System.out.println("sum="+sum);
			pst.close();
			pst_insert.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public void deleteData(Connection sourceConn, Connection targetConn,
			String name, String fromDate, String toDate) {
		// TODO Auto-generated method stub
		System.out.println("从："+fromDate+"   到："+toDate);
		
		Date from=null;

		try {
			from = format_s.parse(fromDate);
		} catch (ParseException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		try{
			EtlLog.progressOneTaskETLLog(targetConn, name, null);//启动日志
			PreparedStatement pst_delete=targetConn.prepareStatement(DELETE);
			java.sql.Date date=new java.sql.Date(from.getYear(),from.getMonth(),1);
			pst_delete.setDate(1,date);
			pst_delete.executeUpdate();
			pst_delete.close();
				
//			EtlLog.finishOneTaskETLLog(targetConn, name, toDate);
		}
		catch(SQLException e){
			
		}
		
	}

	public void updateData(Connection sourceConn, Connection targetConn,
			String name, String fromDate, String toDate) {
		try{
			EtlLog.progressOneTaskETLLog(targetConn, name, null);//启动日志
			
			deleteData(sourceConn, targetConn,
					name, fromDate, toDate);
			insertData(sourceConn, targetConn,
					name, fromDate, toDate);
			
//			EtlLog.finishOneTaskETLLog(targetConn, name, toDate);
		}catch(Exception ex){
			ex.printStackTrace();
		}
		
		
	}
}
